package br.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import br.com.bean.CidadeBean;
import br.com.bean.ClienteBean;


public class ClienteDAO {
	public void inserir(Connection conn, ClienteBean value) throws Exception {


		String sql = "INSERT INTO cliente(id, razao, fantasia, responsavel, telefone, numero, endereco,honorario, licenca, datacad, cnpj, ie, suframa, ramo, cidade,bairro) VALUES (?, ?, ?, ?, ?, ?, ?,?, ?, ?, ?, ?, ?, ?, ?,?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ps.setString(2, value.getRazao());
		ps.setString(3, value.getFantasia());
		ps.setString(4, value.getResponsavel());
		ps.setString(5, value.getTelefone());
		ps.setString(6, value.getNumero());
		ps.setString(7, value.getEndereco());
		ps.setDouble(8, value.getHonorario());
		ps.setDouble(9, value.getLicenca());
		ps.setDate(10, value.getDataCad());
		ps.setString(11, value.getCNPJ());
		ps.setString(12, value.getIE());
		ps.setString(13, value.getSuframa());
		ps.setString(14, value.getRamo());
		ps.setInt(15, value.getCidade().getId());
		ps.setInt(16, value.getBairro().getId());
		
		ps.execute();
		ps.close();
	

	}

	public ArrayList<ClienteBean> lista(Connection conn) throws SQLException {

			String sql = "SELECT id, razao FROM cliente";
			
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			ArrayList<ClienteBean> clienteBean = new ArrayList<ClienteBean>();
			while(rs.next()){
				ClienteBean cliente = new ClienteBean();
				cliente.setId(rs.getInt("id"));
				cliente.setRazao(rs.getString("razao"));
				clienteBean.add(cliente);
			}
			rs.close();
			ps.close();
			return clienteBean;
		}

		
	
}
